//-----------------------------------------------------------------------
// <copyright file="ShippingStatusReportDAOBase.cs" company="Pyramid Consulting">
//     Copyright (c) Pyramid Consulting. All rights reserved.
// </copyright>
//-----------------------------------------------------------------------

using System;
using System.Data;
using System.Collections;
using MyFramework;
using MyFramework.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using MIMS.WRS.Entities;

namespace MIMS.WRS.Data
{    
    public abstract class ShippingStatusReportDAOBase
    {
        #region Common methods
        public virtual ShippingStatusReport CreateShippingStatusReportFromReader(IDataReader reader)
        {
            ShippingStatusReport item = new ShippingStatusReport();
            try
            {
                if (!reader.IsDBNull(reader.GetOrdinal("ID"))) item.ID = (int)reader["ID"];
                if (!reader.IsDBNull(reader.GetOrdinal("REQ"))) item.REQ = (int)reader["REQ"];
                if (!reader.IsDBNull(reader.GetOrdinal("OrderDate"))) item.OrderDate = (DateTime)reader["OrderDate"];
                if (!reader.IsDBNull(reader.GetOrdinal("ContainerSize"))) item.ContainerSize = (string)reader["ContainerSize"];
                if (!reader.IsDBNull(reader.GetOrdinal("LP"))) item.LP = (string)reader["LP"];
                if (!reader.IsDBNull(reader.GetOrdinal("PONo_Office"))) item.PONo_Office = (string)reader["PONo_Office"];
                if (!reader.IsDBNull(reader.GetOrdinal("PONo_Type"))) item.PONo_Type = (string)reader["PONo_Type"];
                if (!reader.IsDBNull(reader.GetOrdinal("PONo_Supplier"))) item.PONo_Supplier = (string)reader["PONo_Supplier"];
                if (!reader.IsDBNull(reader.GetOrdinal("PONo_Number"))) item.PONo_Number = (int)reader["PONo_Number"];
                if (!reader.IsDBNull(reader.GetOrdinal("IsPrimary"))) item.IsPrimary = (string)reader["IsPrimary"];
                if (!reader.IsDBNull(reader.GetOrdinal("PONo_Consolidation"))) item.PONo_Consolidation = (int)reader["PONo_Consolidation"];
                if (!reader.IsDBNull(reader.GetOrdinal("AccNo"))) item.AccNo = (string)reader["AccNo"];
                if (!reader.IsDBNull(reader.GetOrdinal("CustomerOrLocation"))) item.CustomerOrLocation = (string)reader["CustomerOrLocation"];
                if (!reader.IsDBNull(reader.GetOrdinal("FinalDestination"))) item.FinalDestination = (string)reader["FinalDestination"];
                if (!reader.IsDBNull(reader.GetOrdinal("Description"))) item.Description = (string)reader["Description"];
                if (!reader.IsDBNull(reader.GetOrdinal("FactoryETD"))) item.FactoryETD = (DateTime)reader["FactoryETD"];
                if (!reader.IsDBNull(reader.GetOrdinal("ShippingDate"))) item.ShippingDate = (DateTime)reader["ShippingDate"];
                if (!reader.IsDBNull(reader.GetOrdinal("ShippingLine"))) item.ShippingLine = (string)reader["ShippingLine"];
                if (!reader.IsDBNull(reader.GetOrdinal("Vessel"))) item.Vessel = (string)reader["Vessel"];
                if (!reader.IsDBNull(reader.GetOrdinal("ContainerNo"))) item.ContainerNo = (string)reader["ContainerNo"];
                if (!reader.IsDBNull(reader.GetOrdinal("ETA"))) item.ETA = (DateTime)reader["ETA"];
                if (!reader.IsDBNull(reader.GetOrdinal("Week"))) item.Week = (int)reader["Week"];
                if (!reader.IsDBNull(reader.GetOrdinal("ArrivalPort"))) item.ArrivalPort = (string)reader["ArrivalPort"];
                if (!reader.IsDBNull(reader.GetOrdinal("PI"))) item.PI = (string)reader["PI"];
                if (!reader.IsDBNull(reader.GetOrdinal("Payment"))) item.Payment = (string)reader["Payment"];
                if (!reader.IsDBNull(reader.GetOrdinal("DocReceived"))) item.DocReceived = (bool)reader["DocReceived"];
                if (!reader.IsDBNull(reader.GetOrdinal("CompletedDate"))) item.CompletedDate = (DateTime)reader["CompletedDate"];
                if (!reader.IsDBNull(reader.GetOrdinal("BulkType"))) item.BulkType = (string)reader["BulkType"];
                if (!reader.IsDBNull(reader.GetOrdinal("ModifiedDate"))) item.ModifiedDate = (DateTime)reader["ModifiedDate"];
                if (!reader.IsDBNull(reader.GetOrdinal("BulkOrderNo"))) item.BulkOrderNo = (int)reader["BulkOrderNo"];
                if (!reader.IsDBNull(reader.GetOrdinal("PayAuth"))) item.PayAuth = (string)reader["PayAuth"];
                if (!reader.IsDBNull(reader.GetOrdinal("ContainerDeliveried"))) item.ContainerDeliveried = (DateTime)reader["ContainerDeliveried"];
                if (!reader.IsDBNull(reader.GetOrdinal("POInOpera"))) item.POInOpera = (DateTime)reader["POInOpera"];
                if (!reader.IsDBNull(reader.GetOrdinal("InvValue"))) item.InvValue = (decimal)reader["InvValue"];
                if (!reader.IsDBNull(reader.GetOrdinal("PayDate"))) item.PayDate = (DateTime)reader["PayDate"];
                if (!reader.IsDBNull(reader.GetOrdinal("Remarks"))) item.Remarks = (string)reader["Remarks"];
                if (!reader.IsDBNull(reader.GetOrdinal("SInvoiced"))) item.SInvoiced = (int)reader["SInvoiced"];
                if (!reader.IsDBNull(reader.GetOrdinal("SuppOrderNo"))) item.SuppOrderNo = (string)reader["SuppOrderNo"];
                if (!reader.IsDBNull(reader.GetOrdinal("OfficeID"))) item.OfficeID = (int)reader["OfficeID"];
                if (!reader.IsDBNull(reader.GetOrdinal("CustPONo"))) item.CustPONo = (string)reader["CustPONo"];
                if (!reader.IsDBNull(reader.GetOrdinal("OnHold"))) item.OnHold = (bool)reader["OnHold"];
                if (!reader.IsDBNull(reader.GetOrdinal("BulkOrderOfficeID"))) item.BulkOrderOfficeID = (int)reader["BulkOrderOfficeID"];
                if (!reader.IsDBNull(reader.GetOrdinal("SInvoicedPd"))) item.SInvoicedPd = (DateTime)reader["SInvoicedPd"];
                if (!reader.IsDBNull(reader.GetOrdinal("Catalogue"))) item.Catalogue = (string)reader["Catalogue"];
                if (!reader.IsDBNull(reader.GetOrdinal("ShippingLineID"))) item.ShippingLineID = (int)reader["ShippingLineID"];
                if (!reader.IsDBNull(reader.GetOrdinal("CatalogID"))) item.CatalogID = (int)reader["CatalogID"];
                if (!reader.IsDBNull(reader.GetOrdinal("PDepValue"))) item.PDepValue = (decimal)reader["PDepValue"];
                if (!reader.IsDBNull(reader.GetOrdinal("SalesInvoiceValue"))) item.SalesInvoiceValue = (decimal)reader["SalesInvoiceValue"];
                if (!reader.IsDBNull(reader.GetOrdinal("SalesInvoiceDate"))) item.SalesInvoiceDate = (DateTime)reader["SalesInvoiceDate"];
                if (!reader.IsDBNull(reader.GetOrdinal("SalesPaymentDue"))) item.SalesPaymentDue = (DateTime)reader["SalesPaymentDue"];
                if (!reader.IsDBNull(reader.GetOrdinal("SalesOrder"))) item.SalesOrder = (int)reader["SalesOrder"];
                if (!reader.IsDBNull(reader.GetOrdinal("TargetETD"))) item.TargetETD = (DateTime)reader["TargetETD"];
                if (!reader.IsDBNull(reader.GetOrdinal("IsArchived"))) item.IsArchived = (bool)reader["IsArchived"];
                if (!reader.IsDBNull(reader.GetOrdinal("ResponsibleID"))) item.ResponsibleID = (int)reader["ResponsibleID"];
                if (!reader.IsDBNull(reader.GetOrdinal("FactoryETDEnteredDate"))) item.FactoryETDEnteredDate = (DateTime)reader["FactoryETDEnteredDate"];
				try
				{
					if (!reader.IsDBNull(reader.GetOrdinal("FeederVessel"))) item.FeederVessel = (string)reader["FeederVessel"];
					if (!reader.IsDBNull(reader.GetOrdinal("IsNomShip"))) item.IsNomShip = (string)reader["IsNomShip"];
					if (!reader.IsDBNull(reader.GetOrdinal("EstActBlank"))) item.EstActBlank = (string)reader["EstActBlank"];
				}
				catch{}
            }
            catch (Exception ex)
            {
                // log this exception
                Logger.Error(ex);
                // wrap it and rethrow
                throw new DataAccessException(SR.DataAccessCreateShippingStatusReportFromReaderException, ex);
            }
            return item;
        }
        #endregion
        
        #region CreateShippingStatusReport methods
            
        public virtual void CreateShippingStatusReport(ShippingStatusReport shippingStatusReport)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportInsert");
                
                dbCommandWrapper.AddInParameter("@REQ", DbType.Int32, shippingStatusReport.REQ);
                dbCommandWrapper.AddInParameter("@OrderDate", DbType.DateTime, shippingStatusReport.OrderDate);
                dbCommandWrapper.AddInParameter("@ContainerSize", DbType.AnsiString, shippingStatusReport.ContainerSize);
                dbCommandWrapper.AddInParameter("@LP", DbType.AnsiStringFixedLength, shippingStatusReport.LP);
                dbCommandWrapper.AddInParameter("@PONo_Office", DbType.AnsiStringFixedLength, shippingStatusReport.PONo_Office);
                dbCommandWrapper.AddInParameter("@PONo_Type", DbType.AnsiStringFixedLength, shippingStatusReport.PONo_Type);
                dbCommandWrapper.AddInParameter("@PONo_Supplier", DbType.AnsiString, shippingStatusReport.PONo_Supplier);
                dbCommandWrapper.AddInParameter("@PONo_Number", DbType.Int32, shippingStatusReport.PONo_Number);
                dbCommandWrapper.AddInParameter("@IsPrimary", DbType.AnsiStringFixedLength, shippingStatusReport.IsPrimary);
                dbCommandWrapper.AddInParameter("@PONo_Consolidation", DbType.Int32, shippingStatusReport.PONo_Consolidation);
                dbCommandWrapper.AddInParameter("@AccNo", DbType.AnsiString, shippingStatusReport.AccNo);
                dbCommandWrapper.AddInParameter("@CustomerOrLocation", DbType.String, shippingStatusReport.CustomerOrLocation);
                dbCommandWrapper.AddInParameter("@FinalDestination", DbType.String, shippingStatusReport.FinalDestination);
                dbCommandWrapper.AddInParameter("@Description", DbType.String, shippingStatusReport.Description);
                dbCommandWrapper.AddInParameter("@FactoryETD", DbType.DateTime, shippingStatusReport.FactoryETD);
                dbCommandWrapper.AddInParameter("@ShippingDate", DbType.DateTime, shippingStatusReport.ShippingDate);
                dbCommandWrapper.AddInParameter("@ShippingLine", DbType.AnsiString, shippingStatusReport.ShippingLine);
                dbCommandWrapper.AddInParameter("@Vessel", DbType.String, shippingStatusReport.Vessel);
                dbCommandWrapper.AddInParameter("@ContainerNo", DbType.AnsiString, shippingStatusReport.ContainerNo);
                dbCommandWrapper.AddInParameter("@ETA", DbType.DateTime, shippingStatusReport.ETA);
                dbCommandWrapper.AddInParameter("@Week", DbType.Int32, shippingStatusReport.Week);
                dbCommandWrapper.AddInParameter("@ArrivalPort", DbType.String, shippingStatusReport.ArrivalPort);
                dbCommandWrapper.AddInParameter("@PI", DbType.AnsiString, shippingStatusReport.PI);
                dbCommandWrapper.AddInParameter("@Payment", DbType.AnsiStringFixedLength, shippingStatusReport.Payment);
                dbCommandWrapper.AddInParameter("@DocReceived", DbType.Boolean, shippingStatusReport.DocReceived);
                dbCommandWrapper.AddInParameter("@CompletedDate", DbType.DateTime, shippingStatusReport.CompletedDate);
                dbCommandWrapper.AddInParameter("@BulkType", DbType.AnsiString, shippingStatusReport.BulkType);
                dbCommandWrapper.AddInParameter("@ModifiedDate", DbType.DateTime, shippingStatusReport.ModifiedDate);
                dbCommandWrapper.AddInParameter("@BulkOrderNo", DbType.Int32, shippingStatusReport.BulkOrderNo);
                dbCommandWrapper.AddInParameter("@PayAuth", DbType.AnsiStringFixedLength, shippingStatusReport.PayAuth);
                dbCommandWrapper.AddInParameter("@ContainerDeliveried", DbType.DateTime, shippingStatusReport.ContainerDeliveried);
                dbCommandWrapper.AddInParameter("@POInOpera", DbType.DateTime, shippingStatusReport.POInOpera);
                dbCommandWrapper.AddInParameter("@InvValue", DbType.Currency, shippingStatusReport.InvValue);
                dbCommandWrapper.AddInParameter("@PayDate", DbType.DateTime, shippingStatusReport.PayDate);
                dbCommandWrapper.AddInParameter("@Remarks", DbType.String, shippingStatusReport.Remarks);
                dbCommandWrapper.AddInParameter("@SInvoiced", DbType.Int32, shippingStatusReport.SInvoiced);
                dbCommandWrapper.AddInParameter("@SuppOrderNo", DbType.AnsiString, shippingStatusReport.SuppOrderNo);
                dbCommandWrapper.AddInParameter("@OfficeID", DbType.Int32, shippingStatusReport.OfficeID);
                dbCommandWrapper.AddInParameter("@CustPONo", DbType.AnsiString, shippingStatusReport.CustPONo);
                dbCommandWrapper.AddInParameter("@OnHold", DbType.Boolean, shippingStatusReport.OnHold);
                dbCommandWrapper.AddInParameter("@BulkOrderOfficeID", DbType.Int32, shippingStatusReport.BulkOrderOfficeID);
                dbCommandWrapper.AddInParameter("@SInvoicedPd", DbType.DateTime, shippingStatusReport.SInvoicedPd);
                dbCommandWrapper.AddInParameter("@Catalogue", DbType.String, shippingStatusReport.Catalogue);
                dbCommandWrapper.AddInParameter("@ShippingLineID", DbType.Int32, shippingStatusReport.ShippingLineID);
                dbCommandWrapper.AddInParameter("@CatalogID", DbType.Int32, shippingStatusReport.CatalogID);
                dbCommandWrapper.AddInParameter("@PDepValue", DbType.Currency, shippingStatusReport.PDepValue);
                dbCommandWrapper.AddInParameter("@SalesInvoiceValue", DbType.Currency, shippingStatusReport.SalesInvoiceValue);
                dbCommandWrapper.AddInParameter("@SalesInvoiceDate", DbType.DateTime, shippingStatusReport.SalesInvoiceDate);
                dbCommandWrapper.AddInParameter("@SalesPaymentDue", DbType.DateTime, shippingStatusReport.SalesPaymentDue);
                dbCommandWrapper.AddInParameter("@SalesOrder", DbType.Int32, shippingStatusReport.SalesOrder);
                dbCommandWrapper.AddInParameter("@TargetETD", DbType.DateTime, shippingStatusReport.TargetETD);
                dbCommandWrapper.AddInParameter("@IsArchived", DbType.Boolean, shippingStatusReport.IsArchived);
                dbCommandWrapper.AddInParameter("@ResponsibleID", DbType.Int32, shippingStatusReport.ResponsibleID);
                dbCommandWrapper.AddInParameter("@FactoryETDEnteredDate", DbType.DateTime, shippingStatusReport.FactoryETDEnteredDate);
                dbCommandWrapper.AddOutParameter("@ID", DbType.Int32, 0);
                
                DataAccessHelper.ExecuteNonQuery(database, dbCommandWrapper);
                shippingStatusReport.ID = (int)dbCommandWrapper.GetParameterValue("@ID");
            }
            catch (Exception ex)
            {
                // log this exception
                Logger.Error(ex);
                // wrap it and rethrow
                throw new DataAccessException(SR.DataAccessCreateShippingStatusReportException, ex);
            }
        }

        #endregion

        #region UpdateShippingStatusReport methods
        
        public virtual void UpdateShippingStatusReport(ShippingStatusReport shippingStatusReport)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportUpdate");            
                
                dbCommandWrapper.AddInParameter("@ID", DbType.Int32, shippingStatusReport.ID);
                dbCommandWrapper.AddInParameter("@REQ", DbType.Int32, shippingStatusReport.REQ);
                dbCommandWrapper.AddInParameter("@OrderDate", DbType.DateTime, shippingStatusReport.OrderDate);
                dbCommandWrapper.AddInParameter("@ContainerSize", DbType.AnsiString, shippingStatusReport.ContainerSize);
                dbCommandWrapper.AddInParameter("@LP", DbType.AnsiStringFixedLength, shippingStatusReport.LP);
                dbCommandWrapper.AddInParameter("@PONo_Office", DbType.AnsiStringFixedLength, shippingStatusReport.PONo_Office);
                dbCommandWrapper.AddInParameter("@PONo_Type", DbType.AnsiStringFixedLength, shippingStatusReport.PONo_Type);
                dbCommandWrapper.AddInParameter("@PONo_Supplier", DbType.AnsiString, shippingStatusReport.PONo_Supplier);
                dbCommandWrapper.AddInParameter("@PONo_Number", DbType.Int32, shippingStatusReport.PONo_Number);
                dbCommandWrapper.AddInParameter("@IsPrimary", DbType.AnsiStringFixedLength, shippingStatusReport.IsPrimary);
                dbCommandWrapper.AddInParameter("@PONo_Consolidation", DbType.Int32, shippingStatusReport.PONo_Consolidation);
                dbCommandWrapper.AddInParameter("@AccNo", DbType.AnsiString, shippingStatusReport.AccNo);
                dbCommandWrapper.AddInParameter("@CustomerOrLocation", DbType.String, shippingStatusReport.CustomerOrLocation);
                dbCommandWrapper.AddInParameter("@FinalDestination", DbType.String, shippingStatusReport.FinalDestination);
                dbCommandWrapper.AddInParameter("@Description", DbType.String, shippingStatusReport.Description);
                dbCommandWrapper.AddInParameter("@FactoryETD", DbType.DateTime, shippingStatusReport.FactoryETD);
                dbCommandWrapper.AddInParameter("@ShippingDate", DbType.DateTime, shippingStatusReport.ShippingDate);
                dbCommandWrapper.AddInParameter("@ShippingLine", DbType.AnsiString, shippingStatusReport.ShippingLine);
                dbCommandWrapper.AddInParameter("@Vessel", DbType.String, shippingStatusReport.Vessel);
                dbCommandWrapper.AddInParameter("@ContainerNo", DbType.AnsiString, shippingStatusReport.ContainerNo);
                dbCommandWrapper.AddInParameter("@ETA", DbType.DateTime, shippingStatusReport.ETA);
                dbCommandWrapper.AddInParameter("@Week", DbType.Int32, shippingStatusReport.Week);
                dbCommandWrapper.AddInParameter("@ArrivalPort", DbType.String, shippingStatusReport.ArrivalPort);
                dbCommandWrapper.AddInParameter("@PI", DbType.AnsiString, shippingStatusReport.PI);
                dbCommandWrapper.AddInParameter("@Payment", DbType.AnsiStringFixedLength, shippingStatusReport.Payment);
                dbCommandWrapper.AddInParameter("@DocReceived", DbType.Boolean, shippingStatusReport.DocReceived);
                dbCommandWrapper.AddInParameter("@CompletedDate", DbType.DateTime, shippingStatusReport.CompletedDate);
                dbCommandWrapper.AddInParameter("@BulkType", DbType.AnsiString, shippingStatusReport.BulkType);
                dbCommandWrapper.AddInParameter("@ModifiedDate", DbType.DateTime, shippingStatusReport.ModifiedDate);
                dbCommandWrapper.AddInParameter("@BulkOrderNo", DbType.Int32, shippingStatusReport.BulkOrderNo);
                dbCommandWrapper.AddInParameter("@PayAuth", DbType.AnsiStringFixedLength, shippingStatusReport.PayAuth);
                dbCommandWrapper.AddInParameter("@ContainerDeliveried", DbType.DateTime, shippingStatusReport.ContainerDeliveried);
                dbCommandWrapper.AddInParameter("@POInOpera", DbType.DateTime, shippingStatusReport.POInOpera);
                dbCommandWrapper.AddInParameter("@InvValue", DbType.Currency, shippingStatusReport.InvValue);
                dbCommandWrapper.AddInParameter("@PayDate", DbType.DateTime, shippingStatusReport.PayDate);
                dbCommandWrapper.AddInParameter("@Remarks", DbType.String, shippingStatusReport.Remarks);
                dbCommandWrapper.AddInParameter("@SInvoiced", DbType.Int32, shippingStatusReport.SInvoiced);
                dbCommandWrapper.AddInParameter("@SuppOrderNo", DbType.AnsiString, shippingStatusReport.SuppOrderNo);
                dbCommandWrapper.AddInParameter("@OfficeID", DbType.Int32, shippingStatusReport.OfficeID);
                dbCommandWrapper.AddInParameter("@CustPONo", DbType.AnsiString, shippingStatusReport.CustPONo);
                dbCommandWrapper.AddInParameter("@OnHold", DbType.Boolean, shippingStatusReport.OnHold);
                dbCommandWrapper.AddInParameter("@BulkOrderOfficeID", DbType.Int32, shippingStatusReport.BulkOrderOfficeID);
                dbCommandWrapper.AddInParameter("@SInvoicedPd", DbType.DateTime, shippingStatusReport.SInvoicedPd);
                dbCommandWrapper.AddInParameter("@Catalogue", DbType.String, shippingStatusReport.Catalogue);
                dbCommandWrapper.AddInParameter("@ShippingLineID", DbType.Int32, shippingStatusReport.ShippingLineID);
                dbCommandWrapper.AddInParameter("@CatalogID", DbType.Int32, shippingStatusReport.CatalogID);
                dbCommandWrapper.AddInParameter("@PDepValue", DbType.Currency, shippingStatusReport.PDepValue);
                dbCommandWrapper.AddInParameter("@SalesInvoiceValue", DbType.Currency, shippingStatusReport.SalesInvoiceValue);
                dbCommandWrapper.AddInParameter("@SalesInvoiceDate", DbType.DateTime, shippingStatusReport.SalesInvoiceDate);
                dbCommandWrapper.AddInParameter("@SalesPaymentDue", DbType.DateTime, shippingStatusReport.SalesPaymentDue);
                dbCommandWrapper.AddInParameter("@SalesOrder", DbType.Int32, shippingStatusReport.SalesOrder);
                dbCommandWrapper.AddInParameter("@TargetETD", DbType.DateTime, shippingStatusReport.TargetETD);
                dbCommandWrapper.AddInParameter("@IsArchived", DbType.Boolean, shippingStatusReport.IsArchived);
                dbCommandWrapper.AddInParameter("@ResponsibleID", DbType.Int32, shippingStatusReport.ResponsibleID);
                dbCommandWrapper.AddInParameter("@FactoryETDEnteredDate", DbType.DateTime, shippingStatusReport.FactoryETDEnteredDate);
                
                DataAccessHelper.ExecuteNonQuery(database, dbCommandWrapper);
            }
            catch (Exception ex)
            {
                // log this exception
                Logger.Error(ex);
                // wrap it and rethrow
                throw new DataAccessException(SR.DataAccessUpdateShippingStatusReportException, ex);
            }
        }
        
        #endregion

        #region DeleteShippingStatusReport methods
        public virtual void DeleteShippingStatusReport(int id)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportDelete");
                
                dbCommandWrapper.AddInParameter("@ID", DbType.Int32, id);
                
                DataAccessHelper.ExecuteNonQuery(database, dbCommandWrapper);
            }
            catch (Exception ex)
            {
                // log this exception
                Logger.Error(ex);
                // wrap it and rethrow
                throw new DataAccessException(SR.DataAccessDeleteShippingStatusReportException, ex);
            }
        }

        #endregion

        #region GetShippingStatusReport methods
        
        public virtual ShippingStatusReport GetShippingStatusReport(int id)
        {            
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportGet");
                
                dbCommandWrapper.AddInParameter("@ID", DbType.Int32, id);
                
                ShippingStatusReport shippingStatusReport =  null;
                using (IDataReader reader = DataAccessHelper.ExecuteReader(database, dbCommandWrapper))
                {
                    if (reader.Read())
                    {
                        shippingStatusReport = CreateShippingStatusReportFromReader(reader);
                        reader.Close();
                    }
                }
                return shippingStatusReport;
            }
            catch (Exception ex)
            {
                // log this exception
                Logger.Error(ex);
                // wrap it and rethrow
                throw new DataAccessException(SR.DataAccessGetShippingStatusReportException, ex);
            }
        }
        
		public virtual ShippingStatusReport GetShippingStatusReportByReq(int REQ)
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportGetByREQ");
                
				dbCommandWrapper.AddInParameter("@REQ", DbType.Int32, REQ);
                
				ShippingStatusReport shippingStatusReport =  null;
				using (IDataReader reader = DataAccessHelper.ExecuteReader(database, dbCommandWrapper))
				{
					if (reader.Read())
					{
						shippingStatusReport = CreateShippingStatusReportFromReader(reader);
						reader.Close();
					}
				}
				return shippingStatusReport;
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetShippingStatusReportException, ex);
			}
		}

        #endregion

        #region GetShippingStatusReports methods
        public virtual ShippingStatusReportCollection GetShippingStatusReports(ShippingStatusReportColumns orderBy, OrderDirection orderDirection, int page, int pageSize, out int totalRecords)
        {            
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportGetList");
                
                dbCommandWrapper.AddInParameter("@OrderBy", DbType.AnsiString, orderBy.ToString());
                dbCommandWrapper.AddInParameter("@OrderDirection", DbType.AnsiString, orderDirection.ToString());
                dbCommandWrapper.AddInParameter("@Page", DbType.Int32, page);
                dbCommandWrapper.AddInParameter("@PageSize", DbType.Int32, pageSize);
                dbCommandWrapper.AddOutParameter("@TotalRecords", DbType.Int32, 4);
                
                ShippingStatusReportCollection shippingStatusReportCollection = new ShippingStatusReportCollection();
                using (IDataReader reader = DataAccessHelper.ExecuteReader(database, dbCommandWrapper))
                {
                    while (reader.Read())
                    {
                        ShippingStatusReport shippingStatusReport = CreateShippingStatusReportFromReader(reader);
                        shippingStatusReportCollection.Add(shippingStatusReport);
                    }
                    reader.Close();
                }
                totalRecords = (int)dbCommandWrapper.GetParameterValue("@TotalRecords");
                return shippingStatusReportCollection;
            }
            catch (Exception ex)
            {
                // log this exception
                Logger.Error(ex);
                // wrap it and rethrow
                throw new DataAccessException(SR.DataAccessGetShippingStatusReportsException, ex);
            }
        }
        
        public virtual ShippingStatusReportCollection GetShippingStatusReports(ShippingStatusReportColumns orderBy, OrderDirection orderDirection)
        {            
            int totalRecords = 0;
            return GetShippingStatusReports(orderBy, orderDirection, 0, 0, out totalRecords);
        }
        
        #endregion
    }
}